#!/usr/bin/env python
# encoding: utf-8
'''
@author: zhaowenpeng
@contact: winston@peipeiyun.com
@software: garner
@file: pandas_sheet_合并.py
@time: 2020/6/3 4:36 下午
@desc:
'''

# 第一步：调用pandas包
import pandas as pd
# iris = pd.read_excel('杭州亚尊全系产品目录报.xls')#读入数据文件
# keys = list(iris.keys())
# iris_concat = pd.DataFrame()
# for i in keys:
#     iris1 = iris[i]
#     iris_concat = pd.concat([iris_concat,iris1])
# iris_concat.to_excel('yazun02.xlsx')
# #数据保存路径
import traceback

data = []
for index in range(1, 20):
    try:
        sheet_1 = pd.read_excel('0628亚尊品牌数据导入.xlsx', sheet_name=index)
        name = [column for column in sheet_1][0]
        # print(name)
        sheet_1['OE'] = sheet_1[name]
        sheet_1['OE'].replace('-', '', inplace=True)
        sheet_1['OE'].replace(' ', '', inplace=True)
        sheet_1['OE'].replace('.', '', inplace=True)

        # print(sheet_1['OE'])
        # print(sheet_1.iloc[:,0])
        # st = sheet_1.iloc[:, 0]
        # print(type(st))
        data.append(sheet_1['OE'])
        # print(sheet_1)
    except:
        print(traceback.format_exc())
        break
# pd1 = pd.DataFrame()
# s = pd.Series(["a", "b", "c"],
#               name="vals")
# s.to_frame()
pd1 = pd.concat(data, names=['OE'])
# pd1
pd2 = pd1.to_frame('OE')
f = lambda x: str(x).replace("-", "").replace(".", "")
f1 = lambda x: 'YZ-' + x
pd2['OE'] = pd2['OE'].map(f)
pd2['code'] = pd2['OE'].map(f1)
# pd2['OE'].replace('-', '', inplace=True)
pd2.to_excel('亚尊3.xlsx')
